{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using the Excel Application in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "## Overview of the Win32 COM Library\n",
    "To the surprise of many, the entire VBA model is accessible from Python. However, in order to access this model we need to access it using the Windows' API. This can easily be done by leveraging a built in library for Windows's users. This library is called the **Win32Com Library** and with it we have a framework where we can use and publish our own COM (Component Object Model) objects regardless of the language they were implemented in.\n",
    "\n",
    "This is a powerful concept because now we can use COM objects in a consistent way that functions relability without having to understand the details of how the object was created or implemented.\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "### Section One: Creating an Instance Of Excel\n",
    "In order to create an instance of Excel from Python we need to import our `win32com.client` and then we will use the `win32com.client.Dispatch(\"Excel.Application\")` to create our COM object. Now by default just because the object has been created doesn't mean we can see it. Let's set the `Visible` property to `True` so it makes the application visible.\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<win32com.gen_py.Microsoft Excel 16.0 Object Library._Application instance at 0x2088562475080>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import win32com.client\n",
    "\n",
    "#create an instance of Excel & make it visible\n",
    "ExcelApp = win32com.client.Dispatch(\"Excel.Application\")\n",
    "ExcelApp.Visible = True\n",
    "\n",
    "display(ExcelApp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "### Section Two: Add Objects To Our Application\n",
    "Okay we have our Excel Application and it behaves exactly how it would behave in our VBA model, so let's add some more objects we can modify. The first object we will creat is a Workbook object, this mean we will go into our `ExcelApp`, call the `Workbooks` Collection property, and then the `Add()` method to add a new workbook to our collection. Let's do the same for a worksheet, but this time we have to go to our `ExcelWorkbook` object and then the `Worksheets` collection. We have to do this because the `Worksheets` collection exist in our `Workbook` object. Finally, let's define a range of cells and store them in a variable. From here, call the `Value` property and set the value of each cell to `200`. I also made sure to display each object, notice how they're `win32com.gen_py.` objects that belong to our Microsoft Application.\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<win32com.gen_py.None.Workbook>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "<win32com.gen_py.Microsoft Excel 16.0 Object Library._Worksheet instance at 0x2088562476760>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "<win32com.gen_py.Microsoft Excel 16.0 Object Library.Range instance at 0x2088562476704>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "((200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,),\n",
       " (200.0,))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#create a new workbook in the Excel app\n",
    "ExcelWorkbook = ExcelApp.Workbooks.Add()\n",
    "\n",
    "#create a new sheet in the workbook\n",
    "ExcelWrkSht = ExcelWorkbook.Worksheets.Add()\n",
    "\n",
    "# set a reference to a range of cells & then set their value to 200.\n",
    "ExcRng1 = ExcelWrkSht.Range(\"A1:A10\")\n",
    "ExcRng1.Value = 200\n",
    "\n",
    "display(ExcelWorkbook)\n",
    "display(ExcelWrkSht)\n",
    "display(ExcRng1)\n",
    "display(ExcRng1.Value)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "### Section Three: Looping Through Cells\n",
    "Now that we have a range of cells, let's explore how we can loop through them. Well, the first thing we need to recoginze is that our large range of cells is simply made up of smaller individual cells that have their own unique properties and methods. In this example, I'll define a second range of cells but this time using the `Cells` object. Once, I have my range of cells I'll define the value of those cells using the `Value` property. From here, we will print out some info about our range of cells, like the number of cells in that particular range. Then we will use a for loop to loop through each cell, display the address, or display the value of that cell. I also chose, to use to seperate loops because I want to drive home the concept that you can either loop through each cell or define a range of numbers that goes from 0 to the number of cells in our range.\n",
    "***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7\n",
      "$D$1\n",
      "$D$2\n",
      "$D$3\n",
      "$D$4\n",
      "$D$5\n",
      "$D$6\n",
      "$D$7\n",
      "100.0\n",
      "100.0\n",
      "100.0\n",
      "100.0\n",
      "100.0\n",
      "100.0\n",
      "100.0\n"
     ]
    }
   ],
   "source": [
    "# set a reference to a range of cells\n",
    "Cell1 = ExcelWrkSht.Cells(1,4)\n",
    "Cell2 = ExcelWrkSht.Cells(7,4)\n",
    "ExcRng2 = ExcelWrkSht.Range(Cell1,Cell2)\n",
    "ExcRng2.Value = 100\n",
    "\n",
    "# count the cells in our range\n",
    "print(ExcRng2.Cells.Count)\n",
    "\n",
    "# get the cell address using a for loop\n",
    "for cellItem in ExcRng2:\n",
    "    print(cellItem.Address)\n",
    "    \n",
    "# get the cell value using a for loop but this time using a range object.    \n",
    "for i in range(len(ExcRng2)):\n",
    "    print(cellItem.Value)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
